﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class DbHelperSql
    {
        //  private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"];
        //private static string dbConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"];

        private static string dbConnectionString = ConfigurationManager.ConnectionStrings["DbHelperConnectionString"].ConnectionString;


        private SqlConnection connection;

        public DbHelperSql()
        {
            this.connection = CreateConnection(DbHelperSql.dbConnectionString);
        }

        public DbHelperSql(string connectionString)
        {
            this.connection = CreateConnection(connectionString);
        }

        public static SqlConnection CreateConnection(string connectionString)
        {


            SqlConnection dbconn = new SqlConnection(connectionString);
            dbconn.ConnectionString = connectionString;
            return dbconn;
        }



        public static DataSet ExecuteDataSet(SqlCommand cmd)
        {
            // DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            SqlDataAdapter dbDataAdapter = new SqlDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        public static DataTable ExecuteDataTable(string sql)
        {


            SqlCommand cmd = new SqlCommand(sql, DbHelperSql.CreateConnection(dbConnectionString));

            SqlDataAdapter dbDataAdapter = new SqlDataAdapter();
            cmd.Connection.Open();
            //cmd.Connection.BeginTransaction
            dbDataAdapter.SelectCommand = cmd;

            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            cmd.Connection.Close();
            return dataTable;
        }



        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, DbHelperSql.CreateConnection(dbConnectionString));
            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public static int ExecuteNonQuery(string sql)
        {

            SqlCommand cmd = new SqlCommand(sql, DbHelperSql.CreateConnection(dbConnectionString));
            cmd.Connection.Open();
            int ret = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return ret;
        }

        public static object ExecuteScalar(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, DbHelperSql.CreateConnection(dbConnectionString));
            cmd.Connection.Open();
            object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return ret;
        }


        //存储过程
        public static DataTable Execproce(string proName, SqlParameter[] parameters)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = DbHelperSql.CreateConnection(dbConnectionString);
            cmd.Connection.Open();

            cmd.CommandText = proName;
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddRange(parameters);


            SqlDataAdapter dp = new SqlDataAdapter(cmd);
            DataTable dataTable = new DataTable();

            // 填充datatable
            dp.Fill(dataTable);
            cmd.Connection.Close();

            return dataTable;

        }

    }
}
